package com.senior.english.servicesImp;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import com.senior.english.config.Constant;
import com.senior.english.model.ImproveModel;
import com.senior.english.model.IrregularVerbModel;
import com.senior.english.model.ScoreModel;
import com.senior.english.model.ViewAllResultModel;
import com.senior.english.utilities.ManageFile;

public class ScoreService {
	private JdbcTemplate jdbcTemplate;

	/**
	 * @return the jdbcTemplate
	 */
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	/**
	 * @param jdbcTemplate
	 *            the jdbcTemplate to set
	 */
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@SuppressWarnings({ "deprecation", "unchecked" })
	public List<ScoreModel> getScore(int userId, int testId) {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate());
		sjc.withProcedureName("spd_loadScore");
		sjc.returningResultSet("scoreResult",
				ParameterizedBeanPropertyRowMapper
						.newInstance(ScoreModel.class));
		MapSqlParameterSource mp = new MapSqlParameterSource();
		mp.addValue("u_id", userId);
		mp.addValue("t_id", testId);
		Map<String, Object> m = sjc.execute(mp);
		List<ScoreModel> lstScore = (List<ScoreModel>) m.get("scoreResult");
		return lstScore;
	}

	@SuppressWarnings({ "deprecation", "unchecked" })
	public List<ImproveModel> getImprove(int userId, int times) {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate());
		sjc.withProcedureName("spd_loadImprovement");
		sjc.returningResultSet("improveResult",
				ParameterizedBeanPropertyRowMapper
						.newInstance(ImproveModel.class));
		MapSqlParameterSource mp = new MapSqlParameterSource();
		mp.addValue("u_id", userId);
		mp.addValue("times", times);
		Map<String, Object> m = sjc.execute(mp);
		List<ImproveModel> lstScore = (List<ImproveModel>) m
				.get("improveResult");
		return lstScore;
	}

	@SuppressWarnings({ "deprecation", "unchecked" })
	public List<ViewAllResultModel> getUserResult(int userId) {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate());
		sjc.withProcedureName("spd_loadAllResult");
		sjc.returningResultSet("AllUserResult",
				ParameterizedBeanPropertyRowMapper
						.newInstance(ViewAllResultModel.class));
		MapSqlParameterSource mp = new MapSqlParameterSource();
		mp.addValue("u_id", userId);
		Map<String, Object> m = sjc.execute(mp);
		List<ViewAllResultModel> lstScore = (List<ViewAllResultModel>) m
				.get("AllUserResult");
		return lstScore;
	}

	@SuppressWarnings({ "deprecation", "unchecked" })
	public List<ViewAllResultModel> getYear() {
		SimpleJdbcCall sjc = new SimpleJdbcCall(this.getJdbcTemplate());
		sjc.withProcedureName("spd_loadYear");
		sjc.returningResultSet("Years", ParameterizedBeanPropertyRowMapper
				.newInstance(ViewAllResultModel.class));
		Map<String, Object> m = sjc.execute();
		List<ViewAllResultModel> lstScore = (List<ViewAllResultModel>) m
				.get("Years");
		return lstScore;
	}
	
	public List<String> getGrammar(String book){
		return ManageFile.getAllFileNames(book);
	}
	public List<IrregularVerbModel> getIrregularVerb(){
		return ManageFile.readXSLfile(Constant.irrVerb);
	}
	public List<String> getVocabulary(){
		return ManageFile.getAllFileNamesVoc(Constant.vocabulary);
	}
}
